package com.ant.storage.utils;

import com.ant.storage.pojo.OriginalData;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelDataProcessor {
    public static List<OriginalData> processExcelData(String filePath) throws IOException {
        List<OriginalData> originalList = new ArrayList<>();
        FileInputStream file = new FileInputStream(new File(filePath));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

        int idCounter = 1;
        int parentId = 0;
        for (Row row : sheet) {
//            if (row.getRowNum() == 0) {
//                continue; // 跳过表头行
//            }
            String categoryName = getCellValue(row.getCell(4));
            String code = getCombinedCode(row);

            originalList.add(new OriginalData(idCounter++, parentId, categoryName, code));

            // 更新 parentId，如果当前行有中类或小类代码，则更新为当前行的 id
            if (hasCode(row.getCell(2)) || hasCode(row.getCell(3))) {
                parentId = idCounter - 1;
            }
        }

        workbook.close();
        file.close();

        return originalList;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // 使用 DecimalFormat 来格式化数值，避免科学计数法等特殊情况
                java.text.DecimalFormat df = new java.text.DecimalFormat("0");
                return df.format(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return null;
        }
    }

    private static String getCombinedCode(Row row) {
        StringBuilder codeBuilder = new StringBuilder();
        addCodePart(codeBuilder, row.getCell(0));
        addCodePart(codeBuilder, row.getCell(1));
        addCodePart(codeBuilder, row.getCell(2));
        addCodePart(codeBuilder, row.getCell(3));
        return codeBuilder.toString();
    }

    private static void addCodePart(StringBuilder codeBuilder, Cell cell) {
        String codePart = getCellValue(cell);
        if (codePart != null &&!codePart.isEmpty()) {
            if (codeBuilder.length() > 0) {
                codeBuilder.append(codePart);
            } else {
                codeBuilder.append(codePart);
            }
        }
    }

    private static boolean hasCode(Cell cell) {
        String code = getCellValue(cell);
        return code != null &&!code.isEmpty();
    }
}
